{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "677bd6e3-e829-4dba-a5f9-be2dc64b3b33",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import os\n",
    "# os.environ['JAVA_HOME']  = 'D:\\bigdata\\jdk-22.0.1'\n",
    "# os.environ['SPARK_HOME'] = 'D:\\bigdata\\spark-3.5.3-bin-hadoop3'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "be15eabf-93a4-4c62-87f1-ec24fcef54e6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3f6a9912-c55b-478d-922f-5387a476ca83",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on function init in module findspark:\n",
      "\n",
      "init(spark_home=None, python_path=None, edit_rc=False, edit_profile=False)\n",
      "    Make pyspark importable.\n",
      "    \n",
      "    Sets environment variables and adds dependencies to sys.path.\n",
      "    If no Spark location is provided, will try to find an installation.\n",
      "    \n",
      "    Parameters\n",
      "    ----------\n",
      "    spark_home : str, optional, default = None\n",
      "        Path to Spark installation, will try to find automatically\n",
      "        if not provided.\n",
      "    python_path : str, optional, default = None\n",
      "        Path to Python for Spark workers (PYSPARK_PYTHON),\n",
      "        will use the currently running Python if not provided.\n",
      "    edit_rc : bool, optional, default = False\n",
      "        Whether to attempt to persist changes by appending to shell\n",
      "        config.\n",
      "    edit_profile : bool, optional, default = False\n",
      "        Whether to create an IPython startup file to automatically\n",
      "        configure and import pyspark.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "help(findspark.init)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ef5d80e6-1b4a-4a4e-ad30-a45d8133afed",
   "metadata": {},
   "outputs": [],
   "source": [
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d25a286e-88a8-4682-b85a-3f9fa8068304",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as F\n",
    "from pyspark.sql import SparkSession"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1130cf9d-21f2-427a-8cf9-bed01f1065f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nappName里面不能有奇奇怪怪的字符，比如&，不然会报错\\n[JAVA_GATEWAY_EXITED] Java gateway process exited\\n'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark = (\n",
    "    SparkSession\n",
    "    .builder\n",
    "    .appName('read  and analysis novel')#appname里面不能有奇奇怪怪的字符，比如&，不然会报错\n",
    "    # .master('local[*]')\n",
    "    .getOrCreate()\n",
    ")\n",
    "'''\n",
    "appName里面不能有奇奇怪怪的字符，比如&，不然会报错\n",
    "[JAVA_GATEWAY_EXITED] Java gateway process exited\n",
    "'''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "79d928b7-d46c-44d0-9a11-390a3cda3269",
   "metadata": {},
   "outputs": [],
   "source": [
    "# folder_path = r'D:\\bigdata\\spark_ practice\\DataAnalysisWithPythonAndPySpark-Data-trunk\\gutenberg_books\\*.txt'\n",
    "# results = spark.read.text(folder_path)\n",
    "# results.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "3560b006-3763-43eb-af7a-ee683c01b78c",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_path = r'D:\\bigdata\\spark_ practice\\DataAnalysisWithPythonAndPySpark-Data-trunk\\gutenberg_books\\30254-0.txt'\n",
    "df = spark.read.text(file_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3ea25b14-9b06-4ee7-ae32-c01eeeb02480",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = (\n",
    "    df.select(F.split(F.col('value'),' ').alias('line'))\n",
    "    .select(F.explode(F.col('line')).alias('word'))\n",
    "    .select(F.regexp_extract(F.col('word'),\"[a-z']*\",0).alias('word'))\n",
    "    .select(F.lower(F.col('word')).alias('word'))\n",
    "    # .where(F.expr(\"word != ''\"))\n",
    "    .where(F.col('word')!='')\n",
    "    .groupby(F.col('word'))\n",
    "    .count()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "ba5cc763-16df-4bac-ad9a-90928ba61009",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|  word|count|\n",
      "+------+-----+\n",
      "|online|    4|\n",
      "|waters|    3|\n",
      "|  some|  274|\n",
      "| feign|    3|\n",
      "| still|  184|\n",
      "+------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "results.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "243c8658-6301-43b5-96bf-36826ec15c76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|word_count|\n",
      "+----------+\n",
      "|      7984|\n",
      "+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "(\n",
    "    results.select(F.countDistinct(F.col('word')).alias('word_count'))\n",
    "    .show()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f05accfa-a436-4b8b-af18-da0d1ca94e85",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+-----+\n",
      "|  word|count|\n",
      "+------+-----+\n",
      "|online|    4|\n",
      "+------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "def search_word_count(word):\n",
    "    (\n",
    "        results.where(F.col('word')==word)\n",
    "        .show()\n",
    "    )\n",
    "\n",
    "search_word_count('online')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "b6ce6b99-220b-482d-a383-4b0adf4cd946",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7984"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results.distinct().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "7fb8356f-dca2-4749-9125-cf0e7199602b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+-----+\n",
      "|    word|count|\n",
      "+--------+-----+\n",
      "| febrile|    1|\n",
      "|cautious|    1|\n",
      "| jewelry|    1|\n",
      "| elevate|    1|\n",
      "|  outfit|    1|\n",
      "+--------+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "(\n",
    "    results.where(F.col('count')==1)\n",
    "        .show(5)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "cff6d80b-3a46-4c56-8c5c-0b1d6d1854ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+\n",
      "|alpha|count|\n",
      "+-----+-----+\n",
      "|    t|23717|\n",
      "|    a|21244|\n",
      "|    h|13506|\n",
      "|    s|13031|\n",
      "|    w|12659|\n",
      "+-----+-----+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "res = (\n",
    "    df.select(F.split(F.col('value'),' ').alias('line'))\n",
    "    .select(F.explode(F.col('line')).alias('word'))\n",
    "    .select(F.regexp_extract(F.col('word'),\"[a-z']*\",0).alias('word'))\n",
    "    .select(F.lower(F.col('word')).alias('word'))\n",
    "    # .where(F.expr(\"word != ''\"))\n",
    "    .where(F.col('word')!='')\n",
    "    .select(F.substring(F.col('word'),1,1).alias('alpha'))\n",
    "    .groupby('alpha')\n",
    "    .count()\n",
    "    .orderBy('count',ascending=False)\n",
    ")\n",
    "res.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "96ff9aa7-5e60-458c-b47a-ca78cfe805c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+-----+\n",
      "|alpha|count|\n",
      "+-----+-----+\n",
      "|    a|21244|\n",
      "|    o|10967|\n",
      "|    i| 9199|\n",
      "|    e| 4901|\n",
      "|    u| 2717|\n",
      "+-----+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "(\n",
    "    df.select(F.split(F.col('value'),' ').alias('line'))\n",
    "    .select(F.explode(F.col('line')).alias('word'))\n",
    "    .select(F.regexp_extract(F.col('word'),\"[a-z']*\",0).alias('word'))\n",
    "    .select(F.lower(F.col('word')).alias('word'))\n",
    "    # .where(F.expr(\"word != ''\"))\n",
    "    .where(F.col('word')!='')\n",
    "    .select(F.substring(F.col('word'),1,1).alias('alpha'))\n",
    "    .where(F.col('alpha').isin(['a','e','i','o','u']))\n",
    "    .groupby('alpha')\n",
    "    .count()\n",
    "    .orderBy('count',ascending=False)\n",
    ").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa25853e-2234-4a0d-8b8e-597bcb3832ef",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.19"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
